package apj.demo.message.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.sun.xml.internal.bind.v2.model.core.ID;

import apj.demo.model.Department;
import apj.demo.util.JDBCUtil;

@WebServlet("/page")
public class PageServlet extends HttpServlet {
	
	public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request, response);
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
			request.setCharacterEncoding("utf-8");
			response.setCharacterEncoding("utf-8");
			String searchType = request.getParameter("searchType");
			String type = request.getParameter("type");
			String indexStr = request.getParameter("index");
			int index = Integer.parseInt(indexStr);
			List<String> nameList = new ArrayList<String>();
			List<String> MSGList =  new ArrayList<String>();
			List<String> nameChang = new ArrayList<String>();
			try{
				if(type.equals("bill")){
					nameList = getBillName();
					MSGList = getBillDate(index,nameList,type,searchType);
					nameChang = changeName(nameList);
				}else{
					nameList = getMsgName(request);
					MSGList =  getDate(index,nameList,type);
				}
			}catch (Exception e) {
				e.printStackTrace();
			}
			
			com.alibaba.fastjson.JSONObject json = new com.alibaba.fastjson.JSONObject();
			json.put("MSGList", MSGList);
			json.put("nameList", nameList);
			json.put("nameChang",nameChang);
			PrintWriter out = response.getWriter();
			out.print(json);
			out.close();
	}
	
	//获取表列名
		public List<String> getMsgName(HttpServletRequest request){
			List<String> nameList =  null;
			String name = request.getParameter("type");
			
			String indexStr = request.getParameter("index");
			int index = Integer.parseInt(indexStr);
			
			try {
				nameList = searchName(name);
			} catch (Exception e) {
				e.printStackTrace();
			}
			
			return nameList;
		}
		
		public List<String> searchName(String name) throws Exception{
			String sql = "select COLUMN_NAME as name from information_schema.columns where table_name='"+name+"'";
			Connection conn = null;
			PreparedStatement prep = null;
			ResultSet rst = null;
			List<String> nameList = new ArrayList<String>();
			
			try{
				conn = JDBCUtil.getConnection();
				prep = conn.prepareStatement(sql);
				rst = prep.executeQuery();
				
				while(rst.next()){
					String tname = rst.getString("name");
					
					nameList.add(tname);
				}
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				conn.close();
				prep.close();
				rst.close();
			}
			
			return nameList;
		}
		
	public List<String> getDate(int index,List<String> nameList,String type) throws Exception{
		List<String> MSGList = new ArrayList<String>();
		
		String sql = "select * from "+type+" limit "+(index*5-5)+",5";
		
		Connection conn = null;
		PreparedStatement prep = null;
		ResultSet rst = null;
		List<String> l = new ArrayList<String>();
		
		try{
			conn = JDBCUtil.getConnection();
			prep = conn.prepareStatement(sql);
			rst = prep.executeQuery();
			
			while(rst.next()){
				for(int i=0;i<nameList.size();i++){
					l.add(rst.getString(nameList.get(i)));
				}
				MSGList.addAll(l);
				l.clear();
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			conn.close();
			prep.close();
			rst.close();
		}
		return MSGList;
	
	}
	
	public List<String> getBillDate(int index,List<String> nameList,String type,String searchType) throws Exception{
		List<String> MSGList = new ArrayList<String>();
		
		String sql = "select vendor,bill_date,bill_due_date,pay_type from "+type+" where vendor = '"+searchType +"' and bill_finish = 'F' limit "+(index*5-5)+",5";
		
		Connection conn = null;
		PreparedStatement prep = null;
		ResultSet rst = null;
		List<String> l = new ArrayList<String>();
		
		try{
			conn = JDBCUtil.getConnection();
			prep = conn.prepareStatement(sql);
			rst = prep.executeQuery();
			
			while(rst.next()){
				for (int i = 0; i < nameList.size(); i++) {
					l.add(rst.getString(nameList.get(i)));
				}
				MSGList.addAll(l);
				l.clear();
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			conn.close();
			prep.close();
			rst.close();
		}
		
		return MSGList;
	}
	
	public List<String> getBillName(){
		List<String> nameList = new ArrayList<String>();
		
		nameList.add("vendor");
		nameList.add("bill_date");
		nameList.add("bill_due_date");
		nameList.add("pay_type");
		
		return nameList;
	}
	
	public List<String> changeName(List<String> nameList){
		List<String> nameListChange = new ArrayList<String>();
		for(int i=0;i<nameList.size();i++){
			String tname = nameList.get(i);
			switch(tname){
				case "vendor":
					tname = "供应商";
					break;
				case "bill_date":
					tname = "开单时间";
					break;
				case "bill_due_date":
					tname = "有效时间";
					break;
				case "pay_type":
					tname = "付款类型";
					break;
				default:
					break;
			}
			nameListChange.add(tname);
		}
		return nameListChange;
	}
	
}
